The DataReport Designer

Visual Basic 6 is the first version that includes a report writer completely integrated in the IDE. Compared to the more popular Crystal Report, you'll probably find—especially for simpler reports—the new report designer easier to use. But it still lacks several features and can't reasonably replace Crystal Report or other third-party report writers for heavy-duty tasks. By the way, Crystal Report is still included in the package, even though you have to install it manually.

Before using the DataReport designer, you must make it accessible from the IDE, which you do by issuing the Components command from the Project menu, switching to the Designer tab, and ticking the Data Report check box. Alternatively, you can create a new Data Project and let Visual Basic create an instance of the DataReport designer for you.

The DataReport designer works in bound mode only, in the sense that it's able to automatically retrieve the data to be sent to the printer or simply displayed in the preview window. It can export a report to a text file or an HTML file and also supports custom format layouts. The DataReport designer comes with a set of custom controls that you can drop on its surface in the same way as you do with forms and other designers. These controls include lines, shapes, images, and also function fields, which you can use to create summary fields in your reports. Another intriguing feature of this designer is its ability to print in asynchronous mode, which lets the user perform other tasks while the printing proceeds.

The simplest way to create a report using the DataReport designer is in conjunction with the DataEnvironment designer. The DataReport designer supports drag-and-drop operations of DataEnvironment's Command objects, including hierarchical Command objects. The only limitation is that the report can account for just one child Recordset at each nesting level. For all the examples in this chapter, I'll use a hierarchical Command object based on the Orders and Order Details tables in the NWind.mdb database. As usual, the complete sample application is provided on the companion CD.

Here are the steps you should follow to create a report based on the sample hierarchical Command object:

1. Create a hierarchical Command, named Orders, that contains a child Command named Order Details. Ensure that it retrieves the information you're interested in—for example, by binding it to a Hierarchical FlexGrid control on a form and running the application.
2. Create a new instance of the DataReport designer, or use the one provided by default with a Data Project type of Visual Basic project.
3. Bring up the Properties window, let the DataReport's DataSource property point to DataEnvironment1 (or whatever the name of your DataEnvironment is), and then set its DataMember property to Orders.
4. Right-click on the Report Header of the DataReport designer, and select the Retrieve Structure menu command; this will create a Group Header and Group Footer section labeled Orders_Header and Orders_Footer, respectively; between them is a Detail section labeled Order_Details_Detail.

A section represents a block of data that will be repeated for each record in the parent Command object. The first section corresponds to the parent Command object, the second section to its child Command, and so on until you reach the Detail section, which corresponds to the innermost Command object. All the sections except the Detail section are divided into a header section and a footer section, which are printed before and after the information related to the sections pertaining to objects at an inner level. The DataReport designer also includes a Report section (which prints information at the beginning and end of the report) and a Page section (which prints information at the beginning and end of each page). If you don't see these two sections, right-click anywhere on the DataReport designer and select the appropriate menu command.

5. Drag the fields you need from the Orders Command object in the DataEnvironment to the Orders_Header section of the DataReport. Whenever you release the mouse button, a pair of controls, RptLabel and a RptTextBox, appear in the DataReport. When the report is eventually displayed, the RptLabel control produces a constant string with the name of the field (or whatever you assigned to its Caption property), while the RptTextBox control is replaced by the actual contents of the corresponding database field. You can then arrange the fields in the Orders_Header section and delete the RptLabel controls that you don't want to display.
6. Click on the Order Details Command object and drag it onto the DataReport; Visual Basic creates one RtpLabel-RptTextBox control pair for each field in the corresponding Recordset. You can then delete the OrderID field and arrange the others in a row, as displayed in Figure 15-8.
7. Adjust each section's height so that it doesn't take more room than strictly necessary. This is especially important for the Detail section, because it will be repeated for each single record in the Order Detail table. You can also reduce all the sections that don't contain any fields to a null height.
8. What you've done so far is sufficient to see the DataReport in action. Bring up the Project Property Pages dialog box, select DataReport1 as the startup object, and then run the program.

Before moving on to another topic, a couple of notes about the placement of controls are in order. First, you can drop any control in the section that corresponds to the Command object it belongs to, as well as in any section with a deeper nesting level. For example, you can drop the OrderID field from the Orders Command in both the Orders section and the Order_Details section. You can't, however, move the UnitPrice field from the inner Order_Details section to the Order section. Second, you shouldn't drop binary fields or fields containing images from the DataEnvironment onto the DataReport designer; Visual Basic won't generate an error, but it will create a RptTextBox control that contains meaningless characters at run time.

Figure 15-8. The DataReport designer at design time, with the pop-up menu that appears when you right-click on a control.

The controls you have dropped on the DataReport's surface are similar to the standard controls you place on a form, but they belong to a different control library. In fact, you can't drop a standard intrinsic control on a DataReport designer, nor can you place a control from the DataReport control library on a form or another designer. But you can move DataReport controls and align them as you would do with any regular control. You can't use the commands in the standard Format menu, though, and you have to right-click on the control and use the commands in the pop-up menu, as shown in Figure 15-8.

DataReport controls react to the F4 key in the same way regular controls do, by displaying the Properties window. Because the RptLabel and RptTextBox controls are so similar to their standard counterparts, you should already be familiar with most of the properties you find in this window. For example, you can change the DataFormat properties of the txtOrderDate and txtShippedDate controls so that they display their values in long date format. Or you can change the txtOrderID control's BackStyle property to 1-rptBkOpaque and its BackColor property to gray (&HE0E0E0) so that order identifiers are highlighted in the report. RptLabel controls don't expose any Dataxxxx property; they're just cosmetic controls that insert fixed strings in the report.

The only custom property that we haven't seen yet is CanGrow, which applies to both the RptLabel and RptTextBox controls. If this property is True, the control is allowed to expand vertically when its content exceeds the control's width. The default value for this property is False, which causes longer strings to be truncated to the control's width.

Nothing prevents you from adding new controls to the DataReport from the Toolbox rather than from the DataEnvironment designer. In fact, the Toolbox includes a DataReport tab, which contains all the controls in the MSDataReportLib library. In addition to the RptLabel and RptTextBox controls, this library also contains the following items:

• The RptLine and RptShape controls, which allow you to add lines and other elementary shapes to the report, including squares and rectangles (also with round corners), circles, and ovals. You can't change a line's width, but you can create horizontal and vertical lines of any width by using rectangular RptShape controls whose BackStyle property is set to 1rptBkOpaque.
• The RptImage control, which adds static images to the report, such as a company logo. Unfortunately this control can't be bound to a data source, so you can't use it to display images stored in database binary fields.
• The RptFunction control, a textbox variant that can automatically evaluate simple aggregate functions, such as count, sum, average, minimum, maximum, and standard deviation. (This control is described further in the next section.)

For example, drop a horizontal line in the Orders_Footer group, as shown in Figure 15-8. This control will draw a line to separate each group of detail information about an order. Using the BorderStyle property, you can also draw several types of dotted lines.

There are two ways to display calculated fields. The first way, which is suitable for calculated values that depend on other values in the same record, requires that you modify the SELECT command to include the calculated field in the list of fields to be retrieved. In the Orders example, you might have the inner Order Details Command object work with the following SELECT query:

SELECT OrderID, ProductID, UnitPrice, Quantity, Discount, ((UnitPrice*Quantity)*(1-Discount)) AS Total FROM [Order Details]

Then you might add a Total field in the Detail section that lists the total price for each record from the Order Details table. Remember to align the field to the right and allow for the correct number of digits after the decimal point. This way of implementing calculated fields is fairly versatile because you can use all the functions offered by SQL. But it can only work on a record-by-record basis.

Another way to take advantage of SQL is to use a JOIN clause in the SELECT command to retrieve information from other tables. For example, you might transform the ProductID field in the Order Details table into the product's name from the Products table, using the following SELECT in the Order Details Command object:

SELECT [Order Details].OrderID, [Order Details].ProductID, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, (([Order Details].UnitPrice*[Order Details].Quantity)*(1-[Order Details].Discount)) AS Total, Products.ProductName FROM [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID

You can use the same technique to display the customer's name in the Orders_Header section. The sample application, however, achieves the same result by using a different technique, which I'll explain in the "Adding Dynamic Formatting and Lookup Fields" section at the end of this chapter.

The second technique for adding a calculated field is based on RptFunction controls and is suitable for summary fields. For example, let's add a field that evaluates the total value of each order. This requires calculating the sum of the values of the Total field in the Order_Details Command. To do this, you must drop a RptFunction control into the Orders_Footer section—that is, the first footer after the section where the data to be summed is displayed. Then set the new control's DataMember property to Order_Details, its DataField property to Total, its FunctionType to 0rptFuncSum, and its DataFormat property to Currency. Using the same approach, you can add a summary field with the total number of distinct products in the order, by setting DataField to ProductID and FunctionType to 4-rptFuncRCnt.

You're not forced to place a RptFunction control in the footer section that immediately follows the section where the data field is. For example, to evaluate the sum of the Total fields from the Order_Details Command, you can add a RptFunction control in the Report Footer section, and you can add another RptFunction control to calculate the sum of the Freight fields from the Orders section. In any case, you only have to set these controls' DataMember properties to point to the correct Command object. Unfortunately, you can't place a RptFunction control in a Page Footer section, so you can't have totals at the end of each page.

Thanks to the capabilities of the DataEnvironment designer, there is nothing special about preparing a report that groups records. For example, to display a list of customers grouped by country, all you have to do is create a Command object linked to the Customers table, switch to the Grouping tab of its Property Pages dialog box, and group the Command object by its Country field. This operation creates a new Command object with two folders. You can then assign this Command to the DataMember property of a DataReport designer and issue the Retrieve Structure command to let the designer automatically create the necessary sections. The sample application on the companion CD includes a report built using this technique.

You can place controls in a Page Header or Page Footer section, typically to display information about the current page number, the total number of pages, the date and time of the report, and so forth. To do this, right-click in the section of interest, select the Insert Control menu command, and then from a pop-up menu select the information you want to display.

A control created in this way is a RptLabel, which contains special characters in its Caption property. Table 15-1 summarizes the characters with a special meaning when inside a RptLabel control. You can create the control yourself and set a suitable Caption property—for example, Page %p of %P to display the current and the total number of pages—in the same RptLabel control. Figure 15-9 displays the area near the bottom border of a report that includes a page footer, summary fields, and other embellishments that we've seen so far.

Figure 15-9. The DataReport designer at run time; the controls in the window let you print the report, export it to a file, and navigate through its pages.

All the Section objects expose two properties that affect how page breaks are inserted in the report. The ForcePageBreak property determines if a new page should be started before or after the section and can take one of the following values: 0rptPageBreakNone (the default), 1-rptPageBreakBefore (add a page break before printing the section), 2-rptPageBreakAfter (add a page break immediately after the section), or 3-rptPageBreakBeforeAndAfter (add a page break immediately before and after the section).

The other property that affects the page breaks added to the report is the KeepTogether Boolean property. If you set this property to True, the DataReport designer will automatically insert a page break before the section if its contents would overflow to the next page. Both these properties are also exposed by the Report and Page sections, but they're ignored in these cases.

Table 15-1. The special characters accepted in a RtpLabel's Caption property.

Symbol	Description	Symbol	Description
%d	Current date (Short format)	%p	Current page number
%D	Current date (Long format)	%P	Total number of pages
%t	Current time (Short format)	%i	The title of the report
%T	Current time (Long format)	%%	Percentage symbol

The DataReport designer exposes many design-time properties, which you can modify in the Properties window as you would do with any other designer. Most of these properties are also exposed by forms—for example, Caption, Font, WindowState, and ShowInTaskbar—therefore, you already know how to use them. Some of these properties (such as Caption and BorderStyle) affect only the preview window; others (for example, Font) also affect the printed report.

Only a few properties are peculiar to the DataReport designer. The LeftMargin, RightMargin, TopMargin, and BottomMargin properties set and return the size of the printed report margins, whereas ReportWidth determines the width of the printed page. The GridX and GridY properties affect the distance between the division of the control grid at design time and are ignored at run time. All these measures are expressed in twips. The only other custom property of the DataReport designer is Title, which is used to replace the %i placeholder in RptLabel controls, as explained in the previous section, and is also used when displaying dialog boxes at run time.

While you can prepare great reports by merely dropping some controls on a DataReport designer, don't forget that because this is an object that exposes properties, methods, and events, it can be controlled through code at run time. The code can be placed outside the designer—for example, in the form that starts the reporting process—or inside the DataReport module itself. The latter approach lets you create a complex report and encapsulate all the code that manages it in the designer module so that you can easily reuse it in other projects.

The easiest way to print the report is to let the user start the operation interactively by clicking on the leftmost button in the DataReport preview window. Users can pick a printer from the list of installed ones and select a page range and the number of copies they want. They can even print to a file so that they can do the actual printing later. When you enable interactive printing, all you need to do is display the DataReport window, which you can do by using the Show method or (as is rarely done) by designating the DataReport designer as the startup object of the current project. You can use several properties to modify the default appearance of the preview window:

' Display the DataReport in a modal maximized window. DataReport1.WindowState = vbMaximized DataReport1.Show vbModal

You can fine-tune the printing process if you start the print process yourself through code. You will need the PrintReport method of the DataReport designer, which accepts several arguments and returns a Long value:

Cookie = PrintReport([ShowDialog], [Range], [PageFrom], [PageTo])

ShowDialog is a Boolean value that determines whether the designer will display the Print dialog box, and Range can be one of the following values: 0rptRangeAllPages or 1-rptRangeFromTo. If you want to print a page range, you should pass the number of the first and last page to the PageFrom and PageTo arguments, respectively. The PrintReport method starts an asynchronous print process and returns a cookie value, which can be used to refer to the particular print operation. Here's an example:

' Print the first 10 pages of the report without showing a dialog. Dim Cookie As Long Cookie = DataReport1.PrintReport(False, rptRangeFromTo, 1, 10)

Producing the report consists of three subprocesses: the query; the creation of a temporary file; and the actual print, preview, or export operation. The first two are synchronous operations; the third is asynchronous. While the DataReport designer is performing an asynchronous operation, it periodically fires a ProcessingTimeout event, approximately once every second. You can trap this event to let the user cancel a lengthy operation, using a block of code similar to the one shown below.

Private Sub DataReport_ProcessingTimeout(ByVal Seconds As Long, _ Cancel As Boolean,ByVal JobType As MSDataReportLib.AsyncTypeConstants,_ ByVal Cookie As Long) ' Display a message every 20 seconds. Const TIMEOUT = 20 ' The value of Seconds when we displayed the last message. Static LastMessageSecs As Long ' Reset LastMessage if a new print operation is in progress. If Seconds < LastMessageSecs Then LastMessageSecs = 0 ElseIf LastMessageSecs + TIMEOUT <= Seconds Then ' A new timeout interval has elapsed. LastMessageSecs = Seconds ' Ask the user whether the operation should be canceled. If MsgBox("This operation has been started " & Seconds _ & " seconds ago." & vbCr & "Do you want to cancel it?", _ vbYesNo + vbExclamation) = vbYes Then Cancel = True End If End If End Sub

The JobType argument is the type of operation in progress and can be one of the following values: 0-rptAsyncPreview, 1-rptAsyncPrint, or 2-rptAsyncExport. Cookie identifies the particular operation and corresponds to the Long value returned by a PrintReport or ExportReport method.

If you're simply interested in displaying a progress indicator without canceling an asynchronous operation, you can use the AsyncProgress event, which is fired every time a new page is sent to the printer or exported to a file:

Private Sub DataReport_AsyncProgress(ByVal JobType As MSDataReportLib.AsyncTypeConstants, ByVal Cookie As Long, _ ByVal PageCompleted As Long, ByVal TotalPages As Long) ' Display the progress in a Label control on the main form. frmMain.lblStatus = "Printing page " & PageCompleted _ & " of " & TotalPages End Sub

If the DataReport designer can't continue its operations because of an error, it raises an Error event. In this event, you can determine which operation failed and suppress the standard error message by setting the ShowError parameter to False:

Private Sub DataReport_Error(ByVal JobType As MSDataReportLib.AsyncTypeConstants, ByVal Cookie As Long, ByVal ErrObj As MSDataReportLib.RptError, ShowError As Boolean) ' Display your own custom error message box. If JobType = rptAsyncPrint Or JobType = rptAsyncExport Then MsgBox "Error #" & ErrObj.ErrorNumber & vbCr _ & ErrObj.Description, vbCritical ShowError = False End If End Sub

Users can export the current report by clicking on the second button from the left in the DataReport preview window. In the dialog box that appears, they must select a file name, a file type, and a page range, as shown in Figure 15-10. The DataReport designer supports four types of export formats: HTML Text, Unicode, HTML, and Unicode Text. Note that the dialog doesn't display the correct number of total pages; this value depends on the export format and generally doesn't match the number of pages in the preview window (which depends on the Font used in the window itself). Also note that the exported report can't include graphics originated by RptImage and RptShape controls. Horizontal lines are acceptable in HTML reports and appear as rows of hyphens in text reports. Table 15-2 lists the indices, symbolic constants, and string values that you can use to identify the four predefined export formats.

Figure 15-10. The Export dialog box lets you export a report in one of four predefined formats.

The ExportReport method allows you to programmatically export a report, and has the following syntax:

Cookie = ExportReport([FormatIndexOrKey], [FileName], [Overwrite], [ShowDialog], [Range], [PageFrom], [PageTo])

FormatIndexOrKey is a numerical index or a string key that identifies one of the predefined export formats, FileName is the name of the output file, Overwrite is a Boolean value that determines whether an existing file can be overwritten (the default is True), and ShowDialog is a Boolean value that specifies whether the standard export dialog box should be displayed. The remaining arguments have the same meaning as in the PrintReport method. The ExportReport method returns a Long value that can be used to identify this particular operation in a ProcessingTimeout, AsyncProgress, or Error event.

The FormatOrIndexKey is one of the values found in the first three columns of Table 15-2. In fact, you can pass a number in the range from 1 to 4, a rptKeyxxxx symbolic constant, or its corresponding string value. If you omit either the export format or the file name, the Export dialog box is displayed even if you set ShowDialog to False:

' Export all pages to an HTML file in the application's directory. Cookie = DataReport1.ExportReport rptKeyHTML, App.Path & "\Orders", True

The export dialog is also displayed if you specify the name of an existing file and pass Overwrite set to False. You can omit the file extension because the export filter adds the correct extension automatically.

Table 15-2. Indexes, symbolic constants and string values that identify the four predefined export formats.

Index	Symbolic Constant	String	File Filter	Description
1	rptKeyHTML	"key_def_HTML"	*.htm, *.html	HTML
2	rptKeyUnicode-HTML_UTF8	"key_def_Unicode-HTML_UTF8"	*.htm, *.html	Unicode HTML
3	rptKeyText	"key_def_Text"	*.txt	Text
4	rptKeyUnicode-Text	"key_def_Unicode-Text"	*.txt	Unicode text

The export mechanism is fairly sophisticated. In fact, you can define your own export format by adding an ExportFormat object to the ExportFormats collection. The Add method of this collection expects five arguments, which correspond to the properties of the ExportFormat object being created:

ExportFormats.Add Key, FormatType, FileFormatString, FileFilter, Template

Key is the string key that will identify the new export format in the collection. FormatType is one of the following constants: 0-rptFmtHTML, 1-rptFmtText, 2rptFmtUnicodeText, or 3-rptFmtUnicodeHTML_UTF8. FileFormatString is the description that will appear in the File Filter combo box within the Export dialog box, FileFilter is the file filter used for this type of report, and Template is a string that determines how the report is arranged:

Private Sub DataReport_Initialize() ' Create a custom export format. Dim template As String template = "My Custom Text Report" & vbCrLf & vbCrLf _ & rptTagTitle & vbCrLf & vbCrLf _ & rptTagBody ExportFormats.Add "Custom Text", rptFmtText, _ "Custom text format (*.txt)", "*.txt", template End Sub

When creating the Template property, you can use two special strings that will work as placeholders to be replaced by the actual report elements. The DataReport library exposes such strings as symbolic constants: The rptTagTitle constant is replaced by the report title (much like a RptLabel control whose Caption property is set to %i), while rptTagBody is replaced by the report body. When you create template strings for HTML formats, you can enforce any text attribute, as in the following code:

Private Sub DataReport_Initialize() ' Create a custom HTML format for exporting this report. Dim template As String Title = "Orders in May 1999" template = "<HTML>" & vbCrLf & _ "<HEAD>" & vbCrLf & _ "<TITLE>" & rptTagTitle & "</TITLE>" & vbCrLf & _ "<BODY>" & vbCrLf _ & rptTagBody & vbCrLf & _ "</BODY>" & vbCrLf & _ "</HTML>" ExportFormats.Add "Custom HTML", rptFmtHTML, _ "Custom HTML format (*.htm)", "*.htm;*.html", template End Sub

Once you've added a custom ExportFormat object, it appears in the Export dialog box's combo box, and you can select it programmatically as you would a built-in export format:

' Export the first page to an HTML report in custom format. Cookie = DataReport1.ExportReport "Custom Text", App.Path & "\Orders", _ True, False, rptRangeFromTo, 1, 1

You often need to create several similar reports, such as one report that displays all the information in the Employees table and another that hides the confidential data. Because the DataReport is a programmable object, you can, in most cases, accommodate such minor differences with a few lines of code. In fact, you can reference all the controls that make up the report and consequently move them around, change their size and visibility, or assign new values to properties such as Caption, ForeColor, and so on.

Before looking at the implementation details, you must learn how to reference a Section object, using the Sections collection, and to reference a control inside a given section:

' Hide the footer section corresponding to the Orders Command. DataReport1.Sections("Orders_Footer").Visible = False ' Change the background color of the lblTitle control. DataReport1.Sections("Section1").Controls("lblTitle").Caption = "May 99"

You can reference a particular section using its numerical index or its name. When the DataReport is initially created, the default sections have generic names: Section1 is the Report Header, Section2 is the Report Footer, Section3 is the Page Header, and Section4 is the Page Footer. The sections that contain database fields take the names of the Command objects from which they retrieve data. In all cases, however, you can change the section's Name property in the Properties window.

Alas, you can't add controls at run time because the DataReport's Controls collection doesn't support the Add method (unlike the form's Controls collection). To work around this limitation, you have to incorporate all the possible fields when preparing a report and then hide those fields that aren't needed in a particular version of the report. You can also hide an entire section by using the section's Visible property, and you can shrink a section by using its Height property. There's a peculiarity, however: You can't reduce a section's height if the operation would leave one or more controls partially invisible. (This holds true even if the control's Visible property is False.) For this reason, after you make a control invisible, you have to decrease its Top property if you also want to shrink the section it belongs to.

The program on the companion CD puts all these techniques together to create a report in two versions (shown in Figure 15-11), with and without details on each order. To make the report a reusable entity, I've added a Public Boolean property named ShowDetails, which can be assigned from outside the DataReport module before invoking its Show, PrintReport, or ExportReport methods. This is the code inside the DataReport module that implements this feature:

' A private member variable. Dim m_ShowDetails As Boolean Public Property Get ShowDetails() As Boolean ShowDetails = m_ShowDetails End Property Public Property Let ShowDetails(ByVal newValue As Boolean) Dim newTop As Single m_ShowDetails = newValue ' This property affects the visibility of the innermost section. Sections("Order_Details_Detail").Visible = m_ShowDetails ' It also affects the visibility of a few fields in the Orders section. ' This is the actual Top value if controls are visible; 0 otherwise. newTop = IIf(m_ShowDetails, 1870, 0) With Sections("Orders_Header") .Controls("lblProduct").Visible = m_ShowDetails .Controls("lblProduct").Top = newTop .Controls("lblUnitPrice").Visible = m_ShowDetails .Controls("lblUnitPrice").Top = newTop .Controls("lblQty").Visible = m_ShowDetails .Controls("lblQty").Top = newTop .Controls("lblDiscount").Visible = m_ShowDetails .Controls("lblDiscount").Top = newTop .Controls("lblTotal").Visible = m_ShowDetails .Controls("lblTotal").Top = newTop .Controls("shaDetailHeader").Visible = m_ShowDetails .Controls("shaDetailHeader").Top = newTop ' Setting the section's Height to 0 shrinks it as much as possible. .Height = IIf(m_ShowDetails, 2200, 0) End With End Property

Figure 15-11. Two versions of the demonstration report, with and without detail data on each order.

At first sight, it seems that the DataReport design has little to offer highly experienced Visual Basic programmers who have learned to use more powerful report writers such as Crystal Report. The truth is, however, that when you combine the DataReport with the standard ADO binding mechanism, its potential increases markedly.

The key to such power isn't obvious until you remember that you can control the format of bound fields through the Format event of a StdDataFormat object. Because this event fires each time a value is read from the data source, it offers a way to execute your custom code each time a record is about to be displayed on the report. The following example shows how you can use this technique to omit null discount values:

' This is used to trap the instant when a new record is read. Dim WithEvents DiscountFormat As StdDataFormat Private Sub DataReport_Initialize() ' Create a StdDataFormat object, and assign it to the txtDiscount field. Set DiscountFormat = New StdDataFormat Set Sections("Order_Details_Detail").Controls("txtDiscount"). _ DataFormat = DiscountFormat End Sub Private Sub DiscountFormat_Format(ByVal DataValue As _ StdFormat.StdDataValue) ' If the discount is zero, use a Null value instead. If CDbl(DataValue.Value) = 0 Then DataValue.Value = Null End Sub

Unfortunately, the code inside a Format event procedure can't directly modify a control's properties, such as Visible, ForeColor, or BackColor. Nor can it dynamically assign an image to a RptImage control while the report is being processed, which would enable you to overcome the designer's inability to display bitmaps stored in a database. If these limitations were addressed, the DataReport designer would become a tool suitable even for most demanding reporting jobs.

The only other (minor) problem I found with this approach is that the DataValue.TargetObject property contains Nothing when the event fires, so you can't assign the same StdDataFormat object to the DataFormat properties of multiple controls, because you wouldn't have any means to tell which field is being processed.

The demonstration program also shows how you can implement lookup fields using a variant of this mechanism. In its Initialize event, the DataReport opens a Recordset that points to the lookup table, and in the Format event, it transforms the CustomerID value in the Orders table into the value of the CompanyName field in the Customers table:

Dim WithEvents CustFormat As StdDataFormat ' Used to look up the CustomerID field in the Customers table Dim rsCust As New ADODB.Recordset Private Sub DataReport_Initialize() ' Create a new format object, and assign it to the txtCustomer field. Set CustFormat = New StdDataFormat Set Sections("Orders_Header").Controls("txtCustomerName").DataFormat _ = CustFormat ' Open a Recordset on the Customers table. rsCust.Open "Customers", DataEnvironment1.Connection1, adOpenStatic, _ adLockReadOnly, adCmdTable End Sub Private Sub DataReport_Terminate() ' Close the Recordset. rsCust.Close Set rsCust = Nothing End Sub Private Sub CustFormat_Format(ByVal DataValue As StdFormat.StdDataValue) ' Transform a CustomerID value into the customer's CompanyName. rsCust.MoveFirst rsCust.Find "CustomerID='" & DataValue.Value & "'" If rsCust.EOF Then DataValue.Value = Null ' Match not found. Else DataValue.Value = rsCust("CompanyName") ' Match found. End If End Sub

This chapter concludes the part of the book devoted to database programming. At this point, you probably know more than you ever dreamed of knowing about ADO, and above all you're aware of its incredible power and some of its shortcomings. In the next part of the book, I describe how you can take advantage of what you have learned about classes and objects to create ActiveX components and controls. If you're a database programmer, you'll find additional material in Chapter 18 about the inner workings of ADO, including directions for building your own data source classes and OLE DB providers.